Software Vault: The Gold Collection/Software Vault - The Gold Collection (American Databankers) (1993).ISO/cdr11/pdox693.zip/TI154.ASC

Text File | 1992-08-12 | 9KB | 331 lines

  PRODUCT  :  Paradox                                NUMBER  :  154
  VERSION  :  2.0 & up
       OS  :  DOS
     DATE  :  August 12, 1992                          PAGE  :  1/5

    TITLE  :   Variables and Wildcards in PAL Queries




  There are two ways of performing queries in a PAL application.
  The first way is through a script that uses commands that follow
  the interactive Paradox menu.  The other way is through a script
  using the PAL QUERY/ENDQUERY command.  Each method has its
  comparative advantages and disadvantages.  In this document, we
  will call the first method a Menu Equivalent Query and the
  second, a Saved Query Image.

  Menu Equivalent Queries can be saved by selecting {Scripts}
  {BeginRecord} then choosing the keystrokes for filling out a
  query.  This is a typical Menu Equivalent Query on a table called
  SAMPLE:

  {Ask} {Sample} Right Check "John Doe" Enter Check "123 Anystreet"
  DO_IT!

  Saved Query Images are created by filling out a query form
  interactively then selecting {Scripts} {Querysave}.  This is a
  typical example as it appears in the script.

  Query

   Sample |      Name      |      Address        |
          | Check John Doe | Check 123 Anystreet |
          |                |                     |
          |                |                     |

  Endquery

  These two methods are useful for saving time when performing the
  same query repeatedly, but some modifications must be made to use
  variables and wildcard operators in a query.

  First, for Saved Query Images you can use variables by preceding
  them with a tilde (~).  In the example above, if you wanted a
  variable name and address you would change it to the following:




















  PRODUCT  :  Paradox                                NUMBER  :  154
  VERSION  :  2.0 & up
       OS  :  DOS
     DATE  :  August 12, 1992                          PAGE  :  2/5

    TITLE  :   Variables and Wildcards in PAL Queries




  NAME="John Doe"                      ; Assign values to the
  ADDRESS="123 Anystreet"              ; NAME and ADDRESS variables
  Query

   Sample |      Name      |      Address       |
          | Check ~NAME    | Check ~ADDRESS     |
          |                |                    |
          |                |                    |

  Endquery
  DO_IT!

  For this query to work properly, the variables NAME and ADDRESS
  must be assigned values (e.g. "John Doe" and "AnyStreet").  This
  query searches for an exact match based on the values of NAME and
  ADDRESS at the time of the query.  To use wildcard operators, you
  must make the wildcard operator part of the query statement, not
  part of the variable assignment.  For instance, to find all
  addresses on Anystreet, assign, "Anystreet" to the ADDRESS
  variable.  Then type ..~ADDRESS in the Address field of the query
  statement.  For example,

  NAME="John Doe"                      ; Assign values to the
  ADDRESS="123 Anystreet"              ; NAME and ADDRESS variables
  Query

   Sample |      Name      |      Address       |
          | Check ~NAME    | Check ..~ADDRESS   |
          |                |                    |
          |                |                    |

  Endquery
  DO_IT!

  To use variables and wildcard operators in a Menu Equivalent
  Query, there are two methods, the TYPEIN command and a Field
  Assignment statement (e.g. [Name] = NAME).  The TYPEIN command
  simulates typing characters into Paradox from the keyboard.  Its
  effect is similar to a quoted string (e.g. "John Doe"), but it
  allows the characters to be stored in a variable.  For example:

  TBL="sample"                         ; Assign the TBL, NAME
  NAME="john.."                        ;   and ADDRESS variables













  PRODUCT  :  Paradox                                NUMBER  :  154
  VERSION  :  2.0 & up
       OS  :  DOS
     DATE  :  August 12, 1992                          PAGE  :  3/5

    TITLE  :   Variables and Wildcards in PAL Queries




  ADDRESS="123 Anystreet"

  These tools in combination with other PAL commands can create
  very flexible queries.  For example:

  {Ask} SELECT TBL Right Check TYPEIN NAME Enter Check TYPEIN
  ADDRESS DO_IT!

  Note: we used the "Right" command to move to the Name field
  before using the TYPEIN command.

  Like the TYPEIN command, a Field Assignment statement (=) also
  simulates typing the value from the keyboard.  Unlike the TYPEIN
  command, it is not necessary to move to the field before
  performing a Field Assignment statement.  The following is an
  example of how to use a Field assignment statement.  For better
  readability, the commands are placed on different lines and each
  line is commented.

          {Ask} SELECT TBL       ; Select table to query
          Right Check            ; Move right and check Name field
          [Name]=NAME            ; Assign NAME variable to Name
                                 ; field
          Enter                  ; Enter moves cursor to the right
          Check                  ; Check Address field
          [Address]=ADDRESS      ; Assign Address variable to
                                 ; Address
          DO_IT!                 ; Press [F2] to process query

  We also have the flexibility to make the Menu Equivalent query
  perform wildcard searches.  By setting the NAME variable, equal
  to "john.." the query will be a wildcard search in the NAME
  field.  The query still searches for an exact match in the
  Address field.  In contrast to a Saved Query Image, wildcard
  operators can be included as part of the variable assignment.

  To perform this query on a variable table, a tilde variable can
  not be substituted for the table name "Sample."

  To perform the same query on variable tables you need to use a
  Menu Equivalent Query along with the SELECT command.  If you
  wanted the first example to be performed on several tables
  instead of just SAMPLE you would change it to the following:













  PRODUCT  :  Paradox                                NUMBER  :  154
  VERSION  :  2.0 & up
       OS  :  DOS
     DATE  :  August 12, 1992                          PAGE  :  4/5

    TITLE  :   Variables and Wildcards in PAL Queries




  TBL="sample"            ; Assign value to TBL variable

  {Ask} SELECT TBL Right Check "John Doe" Enter Check
  "123 Anystreet" DO_IT!

  Note: Where the first example had the actual table name in curly
  braces "{Sample}", we have placed the SELECT command and the
  variable TBL which is equal to a table name.  Changing the value
  of the TBL variable changes the table that is queried.

  An important issue exists when using Menu Equivalent queries and
  Query Saved images. In your application, you may not want a query
  to be performed using every variable.  For example, you may want
  to search for "John Doe" regardless of what the address is.  To
  do so, you might think that not assigning a value to the ADDRESS
  variable is sufficient.  However, it is not!  When a variable is
  not assigned a value, the variable by default equals null.  Thus,
  the query would search for "John Doe" in the name field AND the
  null value "" in the Address field.

  To perform this query correctly, you must use the ISASSIGNED
  function to determine if the variable has been assigned a value.
  When the variable has been assigned value, the value in the
  variable is placed into the query.  For example,

  TBL="sample"                         ; Assign the TBL variable

  @ 5,5
  ?? "What name would you like to search for? "
  ACCEPT "A20" to NAME                 ; Get NAME from user
  @ 10,5
  ?? "What address would you like to search for? "
  ACCEPT "A35" to ADDRESS              ; Get ADDRESS from user

          {Ask} SELECT TBL
          Right Check
          [Name] = NAME                    ; Assign Name field
          Enter Check
          IF NOT ISASSIGNED(ADDRESS) THEN  ; If Address is assigned
            [Address] = ADDRESS            ;  place into the field
          ENDIF
          DO_IT!                           ; Process query














  PRODUCT  :  Paradox                                NUMBER  :  154
  VERSION  :  2.0 & up
       OS  :  DOS
     DATE  :  August 12, 1992                          PAGE  :  5/5

    TITLE  :   Variables and Wildcards in PAL Queries




  As you can see, we use the ACCEPT command to get values from the
  user, and we have the option of leaving the ADDRESS variable
  empty by using an IF statement and the ISASSIGNED function to
  test for ADDRESS being assigned a value.  If ADDRESS is assigned
  a value then it is assigned to the field.

  For additional commands that can be used in conjunction with
  queries see the PAL User's Guide.

  DISCLAIMER: You have the right to use this technical information
  subject to the terms of the No-Nonsense License Statement that
  you received with the Borland product to which this information
  pertains.